﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using AD.Model;
using System.Data;

namespace AD.DAL
{
    public class PayTradeDAL : DALBase
    {
        public static PayTradeMod CheckIP(PayTradeMod pay)
        {
            try
            {
                string filter = " RewardType=2 and RequestIp='" + pay.RequestIp + "'";
                DataTable dt = QueryFilterToTable(filter);
                if (dt == null || dt.Rows.Count <= 0) return null;
                foreach (DataRow row in dt.Rows)
                {
                    PayTradeMod tmp = SetModel(row);
                    if (tmp != null)
                    {
                        if (GetDay(DateTime.Now, tmp.Commit_Date) < 1)
                            return tmp;
                    }
                }
            }
            catch { }
            return null;
        }
        public static List<PayTradeMod> QueryByPage(string filter, int pageSize, int index, out int maxcount)
        {
            maxcount = 0;
            try
            {
                maxcount = Count(filter);
                //string sql = "select top {0} * from (select top {1} * from tab_users_info order by AutoID asc ) as a order by AutoID desc";
                string sql = "select top {0} * from tab_pay_trade_info where (Commit_Date NOT IN (select top {1} Commit_Date from tab_pay_trade_info order by Commit_Date desc)) order by Commit_Date desc";
                sql = string.Format(sql, pageSize, index * pageSize);
                if (!string.IsNullOrEmpty(filter))
                {
                    sql = "select top {0} * from tab_pay_trade_info where {2} and (Commit_Date NOT IN (select top {1} Commit_Date from tab_pay_trade_info where {2} order by Commit_Date desc)) order by Commit_Date desc";
                    sql = string.Format(sql, pageSize, index * pageSize, filter);
                }
                //sql += " order by Commit_Date desc";
                DataTable dt = ExecuteDataTable(sql);
                if (dt == null || dt.Rows.Count <= 0) return null;
                List<PayTradeMod> accountFlows = new List<PayTradeMod>();
                foreach (DataRow row in dt.Rows)
                {
                    PayTradeMod user = SetModel(row);
                    accountFlows.Add(user);
                }
                return accountFlows;
            }
            catch { return null; }
        }
        public static PayTradeMod QueryByTradeNo(string trade_no)
        {
            try
            {
                string sql = string.Format("Select * from tab_pay_trade_info where Out_Trade_No='{0}'", trade_no);
                DataTable dt = ExecuteDataTable(sql);
                if (dt == null || dt.Rows.Count <= 0) return null;
                foreach (DataRow row in dt.Rows)
                {
                    return SetModel(row);
                }
            }
            catch
            {
            }
            return null;
        }
        public static int Count(string filter)
        {
            return QueryFilterToTable(filter).Rows.Count;
        }

        public static int Insert(PayTradeMod payTrade)
        {
            try
            {
                if (CheckPayTrade(payTrade) != null) return 0;
                string sql = "insert into tab_pay_trade_info(InviteId,RequestIp,Remarks,Balance,RewardPoints,GiftPoints,Rechargepoints,RewardType,Out_Trade_No,UserId,Subject,Body,Total_fee,Commit_Date,Status) values(@InviteId,@RequestIp,@Remarks,@Balance,@RewardPoints,@GiftPoints,@Rechargepoints,@RewardType,@Out_Trade_No,@UserId,@Subject,@Body,@Total_fee,@Commit_Date,@Status)";
                SqlParameter[] sqlParams = GetSqlParameter(payTrade);
                return ExecuteNonQuery(sql, sqlParams);
            }
            catch
            {
                return 0;
            }
        }

        public static PayTradeMod CheckPayTrade(PayTradeMod payTrade)
        {
            return CheckPayTrade(payTrade.Out_Trade_No);
        }

        public static PayTradeMod CheckPayTrade(string trade_no)
        {
            try
            {
                string sql = string.Format("Select * from tab_pay_trade_info where Out_Trade_No='{0}'", trade_no);
                DataTable dt = ExecuteDataTable(sql);
                if (dt == null || dt.Rows.Count <= 0) return null;
                foreach (DataRow row in dt.Rows)
                {
                    return SetModel(row);
                }
            }
            catch
            {
            }
            return null;
        }

        public static PayTradeMod QueryFrist(string filter)
        {
            IList<PayTradeMod> tmp = QueryFilterToList(filter);
            if (tmp != null) return tmp[0];
            return null;
        }

        public static DataTable QueryFilterToTable(string filter)
        {
            try
            {
                string sql = "Select * from tab_pay_trade_info ";
                if (!string.IsNullOrEmpty(filter))
                    sql += " where " + filter;
                sql += " order by Commit_Date Asc";
                return ExecuteDataTable(sql);
            }
            catch
            {
            }
            return null;
        }

        public static List<PayTradeMod> QueryFilterToList(string filter)
        {
            try
            {
                DataTable dt = QueryFilterToTable(filter);
                if (dt == null || dt.Rows.Count <= 0) return null;
                List<PayTradeMod> users = new List<PayTradeMod>();
                foreach (DataRow row in dt.Rows)
                {
                    PayTradeMod user = SetModel(row);
                    users.Add(user);
                }
                return users;
            }
            catch
            {
            }
            return null;
        }

        public static int UpdatePaySuccessStatus(string trade_no)
        {
            try
            {
                string sql = "update tab_pay_trade_info set Status=1 where Out_Trade_No='" + trade_no + "'";
                return ExecuteNonQuery(sql, null);
            }
            catch { }
            return 0;
        }
        public static int UpdatePaySuccessStatus(string trade_no, bool flag)
        {
            try
            {
                string sql = "update tab_pay_trade_info set Status=" + (flag == true ? 1 : 0) + " where Out_Trade_No='" + trade_no + "'";
                return ExecuteNonQuery(sql, null);
            }
            catch { }
            return 0;
        }

        public static int UpdateInMoneyStatus(string trade_no)
        {
            try
            {
                string sql = "update tab_pay_trade_info set IsInMoney=1 where Out_Trade_No='" + trade_no + "'";
                return ExecuteNonQuery(sql, null);
            }
            catch { }
            return 0;
        }

        public static int Delete(string filter)
        {
            try
            {
                string sql = "delete from tab_pay_trade_info ";
                if (!string.IsNullOrEmpty(filter))
                    sql += " where " + filter;
                return ExecuteNonQuery(sql);
            }
            catch { }
            return 0;
        }
        public static int DeleteById(string tradeNo)
        {
            return Delete(" Out_Trade_No='" + tradeNo + "'");
        }

        public static int Delete(PayTradeMod payTrade)
        {
            try
            {
                string sql = string.Format(" Out_Trade_No={0}", payTrade.Out_Trade_No);
                return Delete(sql);
            }
            catch { }
            return 0;
        }

        #region 模型

        static PayTradeMod SetModel(DataRow row)
        {
            PayTradeMod user = new PayTradeMod();
            user.Out_Trade_No = row["Out_Trade_No"].ToString();

            if (row["Balance"] != DBNull.Value)
                user.Balance = int.Parse(row["Balance"].ToString());

            if (row["Body"] != DBNull.Value)
                user.Body = row["Body"].ToString();

            if (row["Commit_Date"] != DBNull.Value)
                user.Commit_Date = DateTime.Parse(row["Commit_Date"].ToString());

            if (row["IsInMoney"] != DBNull.Value)
                user.IsInMoney = bool.Parse(row["IsInMoney"].ToString());

            if (row["Status"] != DBNull.Value)
                user.Status = bool.Parse(row["Status"].ToString());

            if (row["Subject"] != DBNull.Value)
                user.Subject = row["Subject"].ToString();

            if (row["Total_fee"] != DBNull.Value)
                user.Total_fee = double.Parse(row["Total_fee"].ToString());

            if (row["UserId"] != DBNull.Value)
                user.UserId =int.Parse( row["UserId"].ToString());

            if (row["RewardPoints"] != DBNull.Value)
                user.RewardPoints = int.Parse(row["RewardPoints"].ToString());

            if (row["GiftPoints"] != DBNull.Value)
                user.GiftPoints = int.Parse(row["GiftPoints"].ToString());

            if (row["Rechargepoints"] != DBNull.Value)
                user.Rechargepoints = int.Parse(row["Rechargepoints"].ToString());

            if (row["RewardType"] != DBNull.Value)
                user.RewardType = (PayTradeType)int.Parse(row["RewardType"].ToString());

            if (row["InviteId"] != DBNull.Value)
                user.InviteId = row["InviteId"].ToString();

            if (row["Remarks"] != DBNull.Value)
                user.Remarks = row["Remarks"].ToString();

            if (row["RequestIp"] != DBNull.Value)
                user.RequestIp = row["RequestIp"].ToString();

            if (row["Status"] != DBNull.Value)
                user.Status = bool.Parse(row["Status"].ToString());

            if (row["UserId"] != DBNull.Value)
                user.UserId = int.Parse(row["UserId"].ToString());

            return user;
        }

        static SqlParameter[] GetSqlParameter(PayTradeMod user)
        {
            List<SqlParameter> param = new List<SqlParameter>();
            SqlParameter Out_Trade_No = new SqlParameter("@Out_Trade_No", SqlDbType.VarChar, 50, "Out_Trade_No");
            Out_Trade_No.Value = user.Out_Trade_No;
            param.Add(Out_Trade_No);

            SqlParameter Body = new SqlParameter("@Body", SqlDbType.VarChar, 500, "Body");
            Body.Value = user.Body;
            param.Add(Body);

            SqlParameter Balance = new SqlParameter("@Balance", SqlDbType.Int, 4, "Balance");
            Balance.Value = user.Balance;
            param.Add(Balance);

            SqlParameter Commit_Date = new SqlParameter("@Commit_Date", SqlDbType.DateTime, 8, "Commit_Date");
            Commit_Date.Value = user.Commit_Date;
            param.Add(Commit_Date);

            SqlParameter IsInMoney = new SqlParameter("@IsInMoney", SqlDbType.Bit, 1, "IsInMoney");
            IsInMoney.Value = user.IsInMoney;
            param.Add(IsInMoney);

            SqlParameter Status = new SqlParameter("@Status", SqlDbType.Bit, 1, "Status");
            Status.Value = user.Status;
            param.Add(Status);

            SqlParameter Subject = new SqlParameter("@Subject", SqlDbType.VarChar, 50, "Subject");
            Subject.Value = user.Subject;
            param.Add(Subject);

            SqlParameter Total_fee = new SqlParameter("@Total_fee", SqlDbType.Money, 8, "Total_fee");
            Total_fee.Value = user.Total_fee;
            param.Add(Total_fee);

            SqlParameter UserId = new SqlParameter("@UserId", SqlDbType.Int, 4, "UserId");
            UserId.Value = user.UserId;
            param.Add(UserId);

            SqlParameter RewardPoints = new SqlParameter("@RewardPoints", SqlDbType.Int, 4, "RewardPoints");
            RewardPoints.Value = user.RewardPoints;
            param.Add(RewardPoints);

            SqlParameter GiftPoints = new SqlParameter("@GiftPoints", SqlDbType.Int, 4, "GiftPoints");
            GiftPoints.Value = user.GiftPoints;
            param.Add(GiftPoints);

            SqlParameter Rechargepoints = new SqlParameter("@Rechargepoints", SqlDbType.Int, 4, "Rechargepoints");
            Rechargepoints.Value = user.Rechargepoints;
            param.Add(Rechargepoints);

            SqlParameter RewardType = new SqlParameter("@RewardType", SqlDbType.Int, 4, "RewardType");
            RewardType.Value = (int)user.RewardType;
            param.Add(RewardType);

            SqlParameter InviteId = new SqlParameter("@InviteId", SqlDbType.VarChar, 100, "InviteId");
            InviteId.Value = user.InviteId;
            param.Add(InviteId);

            SqlParameter Remarks = new SqlParameter("@Remarks", SqlDbType.VarChar, 100, "Remarks");
            Remarks.Value = user.Remarks;
            param.Add(Remarks);

            SqlParameter RequestIp = new SqlParameter("@RequestIp", SqlDbType.VarChar, 100, "RequestIp");
            RequestIp.Value = user.RequestIp;
            param.Add(RequestIp);

            return param.ToArray();
        }

        #endregion
    }
}